<?php

/**
 * 使用方法：通过new MysqlDB($dbhost, $dbuser, $dbpw, $dbname = '', $charset = '',$pconnect = 0)实例化数据库类
 * 参数：$dbhost(主机名), $dbuser(数据库用户名), $dbpw(数据库密码), $dbname(数据库名称), $charset(编码字符集)
 * BUG:出错信息处理 message()
 */

class MysqlDB
{
	/**
	 * @desc 单例
	 * @see  getinstance()
	 * @var  object
	 */
	private static $instance;
	/**
	 * @desc  MySQL服务器连接ID
	 * @see  connect()
	 * @var resource
	 */
	private $conn = null;
	/**
	 * @desc  数据库名
	 * @see  connect()
	 * @see  select_db()
	 * @var String
	 */
	private $dbname;
	/**
	 * @desc 执行次数
	 * @see  query()
	 * @var resource  or  bool
	 */
	private $querynum = 0;
	/**
	 * @desc 调试开关
	 *
	 * @var Int
	 */
	private $debug = 1;
	private $errormsg=null;
	private $search = array('/union(\s*(\/\*.*\*\/)?\s*)+select/i', '/load_file(\s*(\/\*.*\*\/)?\s*)+\(/i', '/into(\s*(\/\*.*\*\/)?\s*)+outfile/i');
	private $replace = array('union &nbsp; select', 'load_file &nbsp; (', 'into &nbsp; outfile');

	/**
	 * @desc  Construct  function 。
	 *  Call function connect and assign value to $conn
	 *
	 */
	private function __construct(){
		$this->conn = MysqlConn::getinstance()->conn;//创建数据库连接实例
		$dbconfig = DBConfig::getConfig();
		$this->dbname = $dbconfig['dbname'];
	}
	public static function getInstance(){
		if (!(self::$instance instanceof self)) {
            self::$instance = new self();
        }
        return self::$instance;

	}
	/**
	 * @desc  Execute the SQL
	 *
	 * @param String $sql
	 * @param String $type        mysql_query  or   mysql_unbuffered_query
	 * @return Execute  results
	 *
	 */
	function query($sql , $type = '')
	{
		$func = $type == 'UNBUFFERED' ? 'mysql_unbuffered_query' : 'mysql_query';
		if(!($query = @$func($sql , $this->conn)) && $type != 'SILENT')
		{
			$this->message('MySQL Query Error', $sql);
			return false;
		}
		$this->querynum++;
		return $query;
	}
		/**
	 	*功能：实现Select查询
	 	*参数：表内字段串，查询条件串【如'col1,col2','table1.name==table2.name'】,查询项【缺省值NULL】
	 	*返回：以数组的形式返回查询到的所有记录
	 	*备注：查询前要对查询条件做《防注入处理》
		*/
	public function select($table,$where=1,array $select=null)
	{
		$select==null?$cols='*':$cols=explode(',',$select);
		$cols = $this->escape($cols);
		$sql="select $str from `$table` where ".$where;
		$query=$this->query($sql);
		return $query;
	}
		/**
	 	* 功能：实现Select的多表左链查询
	 	* 参数：$select:表内字段串,如‘col1,col2,col3 ’
		* 参数：$where:查询条件串,如'table1.name==table2.name'
		* 参数：2Darray $join左联数组，如$join=array(array('tbl c','c.name==u.name'),...)
	 	* 返回：以数组的形式返回查询到的所有记录
	 	* 备注：查询前要对查询条件做《防注入处理》
		*/
	public function join_select($table,array $join_array2,$where,array $select=null)
	{
		$select==null?$cols='*':$cols=explode(',',$select);
		$cols = $this->escape($cols);
		$join_left = '';$on = '';
		foreach($join_array2 as $join_array){
			$join_left .= $join_array[0].',';
			$on .= $join_array[1].' AND ';
		}
		$lft_join = "left join (".rtrim($join_left,',').")on(".rtrim($on,'AND ').")";
		$sql="select $cols from $table $lft_join where ".$where;
		$query=mysql_query($sql,$this->conn);

		return $query;
	}
	/**
	 * @desc  Insert  SQL
	 *
	 * @param String $tablename
	 * @param  $array  array('filed=>$value)
	 * @return bool
	 */
	function insert($tablename, $array)
	{
		$this->check_fields($tablename, $array);
		return $this->query("INSERT INTO `$tablename`(`".implode('`,`', array_keys($array))."`) VALUES('".implode("','", $array)."')");
	}
	/**
	 * @desc Update SQL
	 *
	 * @param Sting $tablename
	 * @param $array  array('filed'=>$value)
	 * @param String $where
	 * @return bool
	 */
	function update($tablename, $array, $where = '')
	{
		$this->check_fields($tablename, $array);
		if($where)
		{
			$sql = '';
			foreach($array as $k=>$v)
			{
				$sql .= ", `$k`='$v'";
			}
			$sql = substr($sql, 1);
			$sql = "UPDATE `$tablename` SET $sql WHERE $where";
		}
		else
		{
			$sql = "REPLACE INTO `$tablename`(`".implode('`,`', array_keys($array))."`) VALUES('".implode("','", $array)."')";
		}
		return $this->query($sql);
	}
	/**
	 * 删除SQL
	 * 参数$table: 表名
	 * 参数$where：条件
	 */
	public function delete($table,$where){
		$sql="delete from `$table` where ".$where;
		$query=$this->query($sql);
		return $query;
	}
	/**
	 * @desc Get primary KEY
	 *
	 * @param String $table
	 * @return Primary  filed
	 */
	function get_primary($table)
	{
		$result = $this->query("SHOW COLUMNS FROM $table");
		while($r = $this->fetch_array($result))
		{
			if($r['Key'] == 'PRI') break;
		}
		$this->free_result($result);
		return $r['Field'];
	}
	/**
	 * @desc Check fileds
	 *
	 * @param unknown_type $tablename
	 * @param unknown_type $array
	 * @return unknown
	 */
	function check_fields($tablename, $array)
	{
		$fields = $this->get_fields($tablename);
		foreach($array AS $k=>$v)
		{
			if(!in_array($k,$fields))
			{
				$this->message('MySQL Query Error', "Unknown column '$k' in field list");
				return false;
			}
		}
	}
	/**
	  * @desc Get the table fileds
	  *
	  * @param String $table
	  * @return Array
	  */
	function get_fields($table)
	{
		$fields = array();
		$result = $this->query("SHOW COLUMNS FROM $table");
		while($r = $this->fetch_array($result))
		{
			$fields[] = $r['Field'];
		}
		$this->free_result($result);
		return $fields;
	}
	/**
	 * @desc  Get a record from database
	 *
	 * @param String $sql
	 * @param String $type    mysql_query  or   mysql_unbuffered_query
	 * @return   Result
	 */
	function fetch($sql, $type = '')
	{
		$query = $this->query($sql, $type);//, $expires, $dbname);
		$rs = $this->fetch_array($query);
		$this->free_result($query);
		return $rs ;
	}
	/**
	 *  @desc  Fetch an assoc array or num  array from result
	 *
	 * @param resource $query
	 * @param default $result_type   assoc or not
	 * @return   result
	 */
	function fetch_array($query, $result_type = MYSQL_ASSOC)
	{
		return mysql_fetch_array($query, $result_type);
	}

	function affected_rows()
	{
		return mysql_affected_rows($this->conn);
	}
	/**
	  * @desc
	  *
	  * @param unknown_type $query
	  * @return unknown
	  */
	function num_rows($query)
	{
		return mysql_num_rows($query);
	}
	/**
	  * @desc Fetch the num of fileds from result
	  *
	  * @param  $query
	  * @return Int
	  */
	function num_fields($query)
	{
		return mysql_num_fields($query);
	}
	/**
	  * @desc Fetch result data
	  *
	  * @param  $query
	  * @param  $row
	  * @return  array
	  */
	function result($query, $row)
	{
		return @mysql_result($query, $row);
	}
	/**
	 * @desc Free result cache
	 *
	 * @param  $query
	 * @return bool
	 */
	function free_result(&$query)
	{
		return mysql_free_result($query);
	}
	/**
	 * @desc Get the Insert ID
	 *
	 * @return ID
	 */
	function insert_id()
	{
		return mysql_insert_id($this->conn);
	}
	/**
	 * @desc Fetch a row as array from result
	 *
	 * @param  $query
	 * @return array
	 */
	function fetch_row($query)
	{
		return mysql_fetch_row($query);
	}
	/**
	  * @desc Escape the special char from string
	  *
	  * @param $string
	  * @return  Escape string
	  */
	function escape($string)
	{
		if(!is_array($string)) return str_replace(array('\n', '\r'), array(chr(10), chr(13)), mysql_real_escape_string(preg_replace($this->search, $this->replace, $string), $this->conn));
		foreach($string as $key=>$val) $string[$key] = $this->escape($val);
		return $string;
	}

	/**
	 * where 等式防注入格式化方法
	 */
	 function quoteInto($equation,$value)
	 {
	 	$value = trim($this->escape($value));
	 	return sprintf($equation,$value);
	 }
	/**
	  * @desc  Show talbe 's  informations
	  *
	  * @param String $table
	  * @return Array
	  */
	function table_status($table)
	{
		return $this->fetch("SHOW TABLE STATUS LIKE '$table'");
	}
	/**
	  * @desc  Show talbe info
	  *
	  * @return Array
	  */
	function tables()
	{
		$tables = array();
		$result = $this->query("SHOW TABLES");
		while($r = $this->fetch_array($result))
		{
			$tables[] = $r['Tables_in_'.$this->dbname];
		}
		$this->free_result($result);
		return $tables;
	}
	/**
	 * @desc check talbe exists or not
	 *
	 * @param String $table
	 * @return bool
	 */
	function table_exists($table)
	{
		$tables = $this->tables($table);
		return in_array($table, $tables);
	}
	/**
	 * @desc check filed  exists or not
	 *
	 * @param String $table
	 * @param String $field
	 * @return bool
	 */
	function field_exists($table, $field)
	{
		$fields = $this->get_fields($table);
		return in_array($field, $fields);
	}
	/**
	  * @desc Fetch MySQL server info
	  *
	  * @return Array
	  */
	function version()
	{
		return mysql_get_server_info($this->conn);
	}
	/**
	 * @desc Close  link
	 *
	 * @return bool
	 */
	function close()
	{
		return mysql_close($this->conn);
	}
	/**
	  * @desc Get error  as text
	  *
	  * @return String
	  */
	function error()
	{
		return @mysql_error($this->conn);
	}
	/**
     * @desc Get error CODE
     *
     * @return Int
     */
	function errno()
	{
		return intval(@mysql_errno($this->conn)) ;
	}
   /**
    * @desc Errors message
    *
    * @param  $message
    * @param  $sql
    */
	function message($message = '', $sql = '')
	{
		$this->errormsg = "<b>MySQL Query : </b>$sql <br /><b> MySQL Error : </b>".$this->error()." <br /> <b>MySQL Errno : </b>".$this->errno()." <br /><b> Message : </b> $message";
		if($this->debug)//如果开启调试，当且仅当定义过IN_ADMIN常量或DEBUG常量为1时，输出错误信息
		{
			$msg = (defined('IN_ADMIN') || DEBUG) ? $this->errormsg : "Bad Request. $LANG[illegal_request_return]";
			echo '<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>'.$msg.'</span></div>';
			exit;
		}
	}
}